# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql
import re


class Clean_Pipeline:
    """数据清洗"""

    def process_item(self, item, spider):

        if item.get('room_data'):
            if item['room_data']:
                return self.clean_room(item)
        else:
            return item

    def clean_room(self, item):
        items = item['room_data']

        for key, value in items.items():
            items[key] = value.strip().replace(' ', '')
            if '暂无数据' in value:
                items[key] = ''

        # 日期转换，以天为单位
        up_date = re.findall(r'\d+', items['up_date'])
        if len(up_date) == 1:
            if '天' in items['up_date']:
                items['up_date'] = int(up_date[0])
                pass
            elif '月' in items['up_date']:
                items['up_date'] = int(up_date[0]) * 30
                pass
            elif '年' in items['up_date']:
                items['up_date'] = int(up_date[0]) * 365
        elif '今' in items['up_date']:
            items['up_date'] = 1

        # 关注人数
        follows = re.findall(r'\d+', items['follows'])
        if len(follows) == 1:
            items['follows'] = int(follows[0])
        else:
            items['follows'] = -1

        # 转换总价格，以元为单位
        total_price = re.findall(r'\d+', items['total_price'])
        if len(total_price) == 1:
            items['total_price'] = int(total_price[0]) * 10000
        elif len(total_price) == 2:
            items['total_price'] = int(total_price[0]) * 10000 + int(total_price[1]) * 1000
        else:
            items['total_price'] = -1

        # 每平方多少元
        unit_price = re.findall(r'\d+', items['unit_price'])
        if len(unit_price) == 1:
            items['unit_price'] = int(unit_price[0])
        elif len(unit_price) == 2:
            items['unit_price'] = int(unit_price[0]) * 1000 + int(unit_price[1])
        else:
            items['unit_price'] = -1
        print(items)
        return {'room_data': items}


class Mysql_Pipeline:
    """数据存储"""

    def __init__(self, MYSQL, CITY_TABLE, REGION_TABLE, ROOM_TABLE):
        self.MYSQL = MYSQL
        self.CITY_TABLE = CITY_TABLE
        self.REGION_TABLE = REGION_TABLE
        self.ROOM_TABLE = ROOM_TABLE

    @classmethod
    def from_crawler(cls, crawler):
        return cls(MYSQL=crawler.settings.get('MYSQL'),
                   CITY_TABLE=crawler.settings.get('CITY_TABLE'),
                   ROOM_TABLE=crawler.settings.get('ROOM_TABLE'),
                   REGION_TABLE=crawler.settings.get('REGION_TABLE'))

    def open_spider(self, sipder):
        self.connect = pymysql.connect(**self.MYSQL)
        self.cursor = self.connect.cursor()
        self.create_table()

    def close_spider(self, sipder):
        self.connect.close()

    def process_item(self, item, spider):

        if item.get('city_data'):
            self.insert(self.CITY_TABLE, item['city_data'])
            return item
        if item.get('region_data'):
            self.insert(self.REGION_TABLE, item['region_data'])
            return item
        if item.get('room_data'):
            self.insert(self.ROOM_TABLE, item['room_data'])
            return item

    def create_table(self):
        city_sql = f"""create table if not exists `{self.CITY_TABLE}` (
                                `city_id` varchar(64) not null comment "城市id",
                                `city_name` varchar(50) not null comment "城市名",
                                `city_url` varchar(64) not null comment "城市链接",
                                primary key (`city_id`))"""
        region_sql = f"""create table if not exists `{self.REGION_TABLE}` (
                                        `region_id` varchar(64) not null comment "区域id",
                                        `city_id` varchar(64) not null comment "城市id",
                                        `region_name` varchar(50) not null comment "区域名",
                                        `region_url` varchar(64) not null comment "区域链接",
                                        primary key (`region_id`))"""
        room_sql = f"""create table if not exists `{self.ROOM_TABLE}` (
                                `room_id` varchar(64) not null comment "二手房id",
                                `region_id` varchar(64) not null comment "区域id",
                                `title` varchar(64) not null comment "标题",
                                `room_url` varchar(64) not null comment "详情页链接",
                                `cell_name` varchar(64) not null comment "小区名称",
                                `area` varchar(64) not null comment "所在区域",
                                `follows` int(64) not null comment "关注人数",
                                `up_date` int(64) not null comment "发布时间",
                                `total_price` int(64) not null comment "总价格",
                                `unit_price` int(64) not null comment "面积的价格",
                                `visitTime` varchar(64) not null comment "看房时间",
                                `house_type` varchar(64) not null comment "房屋户型",
                                `floor` varchar(64) not null comment "所在楼层",
                                `floor_area` varchar(64) not null comment "建筑面积",
                                `house_structure` varchar(64) not null comment "户型结构",
                                `internal_area` varchar(64) not null comment "套内面积",
                                `building_type` varchar(64) not null comment "建筑类型",
                                `orientation` varchar(64) not null comment "房屋朝向",
                                `building_structure` varchar(64) not null comment "建筑结构",
                                `decoration` varchar(64) not null comment "装修情况",
                                `elevator_family` varchar(64) not null comment "梯户比例",
                                `elevator` varchar(64) not null comment "配备电梯",
                                primary key (`room_id`))"""
        try:
            self.cursor.execute(city_sql)
            self.cursor.execute(region_sql)
            self.cursor.execute(room_sql)
            print("创建表成功/已经存在")
        except Exception as e:
            print(f"创建数据表出错，错误：{e}")

    def insert(self, table, item):
        """插入数据"""
        # print(table)
        # print(item)
        try:
            keys = ','.join(item.keys())
            values = ','.join(['% s'] * len(item))
            sql = f"insert ignore into {table} ({keys}) value ({values})"
            # print(sql)
            # print(tuple(item.values()))
            self.cursor.execute(sql, tuple(item.values()))
            self.connect.commit()
        except Exception as e:
            print(f"插入数据/修改数据报错，错误:{e}")
            self.connect.rollback()

